﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZHDJ.Core;
using ZHDJ.Core.Log;
using ZHDJ.Core.Util;
using ZHDJ.Data.Models.Common;

namespace ZHDJ.Data.BLL
{
    public class OnlineStudySystem
    {
        #region 学习系统用户同步
        //A115A58E6C40DA1887F9  密码：e123456
        /// <summary>
        /// 学习系统用户同步
        /// </summary>
        public static void SyncData()
        {
            //SqlServerHelper.ConnectionKey = "AppExamV5";
            var tempSql = @" insert UserInfo (UserCode,UserName,UserPwd,DeptID,Worker,RacingFen,UserMember) 
                                        values('{0}','{1}','A115A58E6C40DA1887F9',13,'考生',-1,1);
                              INSERT [dbo].[UserPower] ([UserCode], [AddNews], [ManagNews], [AddForum], [ManagForum], [AddPoll], [ManagPoll], [AddRubric], [ManagRubric], [AddSubject], [ManagSubject], [AddText], [ManagText], [AddLession], [ManagLession], [CheckRubric], [AddPaper], [ManagPaper], [CheckPaper], [PaperTemple], [LookPaper], [CheckRubricF], [CheckRubricO], [CheckRubricA], [CheckRubricC], [CheckRubricT], [CheckRubricAN], [CheckRubricJ], [ManagExam], [ManagUser], [ManagMenu], [ManagSysSet], [ManagFolder], [ManagPower], [ManagRacing], [CheckPaperID], [LookPaperID], [ZiManag], [AddStudyPlan], [ManagStudyPlan], [AddExam]) 
VALUES (N'{0}', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'N', NULL, NULL, N'N', N'N', N'N', N'N')
;   ";
            var dt = GetNotSyncUser().Tables[0];
            //var sql = new StringBuilder();
            foreach (DataRow dr in dt.Rows)
            {
                var sql = string.Format(tempSql, dr["UserNo"], dr["UserName"]);
                SqlServerHelper.ExecuteNonQuery(sql.ToString());
            }
            //if (!string.IsNullOrEmpty(sql.ToString()))
               // SqlServerHelper.ExecuteNonQuery(sql.ToString());
        }

        private static DataSet GetNotSyncUser()
        {
            var sql = string.Format(@"select UserNo,UserName from tblpartyorgmember where userno not in ({0})", GetExamUserIds());
            return MySqlHelper.ExecuteDataSet(sql);
        }

        private static string GetExamUserIds()
        {
            var dt = GetExamUser().Tables[0];
            if (dt.Rows.Count == 0)
            {
                return "''";
            }
            var texts = new List<string>();
            foreach (DataRow dataRow in dt.Rows)
            {
                if (dataRow["UserCode"].ToString() != "")
                {
                    texts.Add(dataRow["UserCode"].ToString());
                }
            }
            return "'" + (texts.Count < 1 ? "" : string.Join("','", texts.ToArray())) + "'";
        }
        private static DataSet GetExamUser()
        {
            var sql = @"select UserCode from UserInfo";
            return SqlServerHelper.ExecuteDataSet(sql);
        }

        /// <summary>
        /// 同步学分（按10%）
        /// </summary>
        public static void SyncStudyFen()
        {
            var fenSum = GetStudyFen();
            var sql = string.Format(@"delete from tblpartyobjectscore  where Category='"+ SysEnum.PartyScoreCategory.OnlineStudySystem+ "'  ");
             MySqlHelper.ExecuteNonQuery(sql);

            using (var DB = new DBInstance().DB)
            {
                try
                {
                    foreach (DataRow dr in fenSum.Tables[0].Rows)
                    {
                        var loginid = GetParytLoginID(dr["UserCode"].ToString());
                        if (string.IsNullOrEmpty(loginid))
                            continue;
                        var s = new PartyObjectScore();
                        DB.PartyObjectScores.Add(s);
                        s.ID = StringHelper.NewID;
                        s.ObjectID = loginid;
                        s.ObjectType = "Member";
                        s.Score = Convert.ToDecimal(Math.Round(Convert.ToDouble(dr["Fen"]) * 0.1, 2));
                        s.ScoreDate = DateTime.Now;
                        s.ScoreType = "";
                        s.SourceType = "Auto";
                        s.Category = SysEnum.PartyScoreCategory.OnlineStudySystem.ToString();
                        s.CreateBy = "admin";
                        s.CreateTime = DateTime.Now;
                        s.Description = "在线考试的学分融合到平台的积分[" + Math.Round(Convert.ToDouble(dr["Fen"])) + "]，按百分之十";
                        s.Flag = "";
                        s.ForeignID = "";
                        DB.SaveChanges();
                    };
                }
                catch (Exception ex)
                {

                    LogHelper.Error(ex);
                }
                
            }
        }


        /// <summary>
        /// 同步学分（按10%）
        /// </summary>
        public static void SyncExamFen()
        {
            var fenSum = GetExamFen();
            var sql = string.Format(@"delete from tblpartyobjectscore  where Category='" + SysEnum.PartyScoreCategory.OnlineExamning + "'  ");
            MySqlHelper.ExecuteNonQuery(sql);

            using (var DB = new DBInstance().DB)
            {
                try
                {
                    foreach (DataRow dr in fenSum.Tables[0].Rows)
                    {
                        var loginid = GetParytLoginID(dr["UserCode"].ToString());
                        if (string.IsNullOrEmpty(loginid))
                            continue;
                        var s = new PartyObjectScore();
                        DB.PartyObjectScores.Add(s);
                        s.ID = StringHelper.NewID;
                        s.ObjectID = loginid;
                        s.ObjectType = "Member";
                        s.Score = Convert.ToDecimal(Math.Round(Convert.ToDouble(dr["Fen"]) * 0.1, 2));
                        s.ScoreDate = DateTime.Now;
                        s.ScoreType = "";
                        s.SourceType = "Auto";
                        s.Category = SysEnum.PartyScoreCategory.OnlineExamning.ToString();
                        s.CreateBy = "admin";
                        s.CreateTime = DateTime.Now;
                        s.Description = "在线考试的考试得分融合到平台的积分[" + Math.Round(Convert.ToDouble(dr["Fen"])) + "]，按百分之十";
                        s.Flag = "";
                        s.ForeignID = "";
                        DB.SaveChanges();
                    };
                }
                catch (Exception ex)
                {

                    LogHelper.Error(ex);
                }

            }
        }
        private static string GetParytLoginID(string userno)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "userno",Value = userno},
               
            };
            var sql = string.Format(@"select Loginid from tblpartyorgmember where userno=?userno   ");
            var dt = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            Object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return obj.ToString();
            else return "";
        }
        private static string GetParytUserId()
        {
            var sql = string.Format(@"select UserNo from tblpartyorgmember where runsate=0  ");
            var dt= MySqlHelper.ExecuteDataSet(sql).Tables[0];
            if (dt.Rows.Count == 0)
            {
                return "''";
            }
            var texts = new List<string>();
            foreach (DataRow dataRow in dt.Rows)
            {
                if (dataRow["UserNo"].ToString() != "")
                {
                    texts.Add(dataRow["UserNo"].ToString());
                }
            }
            return " '" + (texts.Count < 1 ? "" : string.Join("','", texts.ToArray())) + "' ";
        }
        /// <summary>
        /// 获取学员学分得分
        /// </summary>
        /// <returns></returns>
        private static DataSet GetStudyFen()
        {
            
            var sql = @"SELECT UserCode ,SUM(StudyFen) Fen FROM UserStudyFen where UserCode in ("+ GetParytUserId()+@")
                              GROUP  BY UserCode";
            return SqlServerHelper.ExecuteDataSet(sql);
        }


        /// <summary>
        /// 获取在线考试得分
        /// </summary>
        /// <returns></returns>
        private static DataSet GetExamFen()
        {
            var sql = @"select UserCode, SUM(GetCountFen) Fen from ExamUser where UserCode in (" + GetParytUserId() 
                + @") and Flag <> 'A' and Flag <> 'B' group by UserCode";
            return SqlServerHelper.ExecuteDataSet(sql);
        }
        #endregion
    }
}
